﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using HZ.MBSM.DBUtility;
using System.Text;

public partial class Agency_Tra_NoAreaPSNext : System.Web.UI.Page
{


    protected void Page_Load(object sender, EventArgs e)
    {
        //if (HZ.MBSM.BLL.Login.CheckAssert(this.UseAccountType))
        //{
        //HZ.MBSM.Model.Account _Account = (HZ.MBSM.Model.Account)Session["Account"];
        //if (_Account != null)
        //{
        //    _strAccountid = _Account.AccountId.ToString();


        GridViewShow();




    }
    //}
    private void GridViewShow()
    {
        int i = 0;
        Db db = new Db();
        DbPage dbPage = new DbPage(db);
        dbPage.Table = " SupplyPlan_Goods  ,SupplyPlan_GoodsNext ";                                               //要查询的表明
        dbPage.Key = "SupplyPlan_Goods.goodsid  ";
        dbPage.SelectFieldGroup = "SupplyPlan_Goods.goodsid ,SupplyPlan_Goods.productname,SupplyPlan_Goods.companyname_sc";//表的主键    
        dbPage.Where = fctConditions();                                        //查询条件    
        dbPage.SelectField = @" SupplyPlan_Goods.goodsid,SupplyPlan_Goods.productname ,SupplyPlan_Goods.companyname_sc ,sum(case areaid when 530100 then 1 else 0 end) as 昆明市,
sum(case areaid when 530300 then 1 else 0 end) as 曲靖市,
sum(case areaid when 530400 then 1 else 0 end) as 玉溪市,
sum(case areaid when 530500 then 1 else 0 end) as 保山市,
sum(case areaid when 530600 then 1 else 0 end) as 昭通市,
sum(case areaid when 530700 then 1 else 0 end) as 丽江市,
sum(case areaid when 530800 then 1 else 0 end) as 思茅市,
sum(case areaid when 530900 then 1 else 0 end) as 临沧市,
sum(case areaid when 532300 then 1 else 0 end) as 楚雄彝族自治州,
sum(case areaid when 532500 then 1 else 0 end) as 红河哈尼族彝族自治州,
sum(case areaid when 532600 then 1 else 0 end) as 文山壮族苗族自治州,
sum(case areaid when 532800 then 1 else 0 end) as 西双版纳傣族自治州,
sum(case areaid when 532900 then 1 else 0 end) as 大理白族自治州,
sum(case areaid when 533100 then 1 else 0 end) as 德宏傣族景颇族自治州,
sum(case areaid when 533300 then 1 else 0 end) as 怒江傈僳族自治州,
sum(case areaid when 533400 then 1 else 0 end) as 迪庆藏族自治州,
sum(case areaid when 411700 then 1 else 0 end) as 驻马店市,
sum(case areaid when 411800 then 1 else 0 end) as 济源市
 ";//要显示的字段

        //530100	昆明市
        //530300	曲靖市
        //530400	玉溪市
        //530500	保山市
        //530600	昭通市
        //530700	丽江市
        //530800	思茅市
        //530900	临沧市
        //532300	楚雄彝族自治州
        //532500	红河哈尼族彝族自治州
        //532600	文山壮族苗族自治州
        //532800	西双版纳傣族自治州
        //532900	大理白族自治州
        //533100	德宏傣族景颇族自治州
        //533300	怒江傈僳族自治州
        //533400	迪庆藏族自治州

        //要显示第几页   
        //没有显示记录条数   
        dbPage.OrderBy = "goodsid";

        dbPage.PageIndex = SysFun.ToInt(Request["CurrentlyPageIndex"]);
        dbPage.PageSize = 20;
        this.GridView1.DataSource = dbPage.ExecuteReaderGroup(30);
        this.GridView1.DataBind();
        this.DbPageFlip1.DbPage = dbPage;
        db.Close();
    }
    public string fctConditions()
    {
        string strmap = " SupplyPlan_Goods.SupplyGoodsId=SupplyPlan_GoodsNext.SupplyGoodsId and isuse=1 and";
        if (this.goodsid.Text.ToString() != "")
        {
            strmap += " SupplyPlan_Goods.goodsid='" + this.goodsid.Text + "' and";
        }
        if (this.productname.Text.ToString() != "")
        {
            strmap += " SupplyPlan_Goods.productname like '%" + this.productname.Text.ToString() + "%' and";
        }
        strmap = strmap.Substring(0, strmap.Length - 3);

        return strmap;
    }
    private void printWord()
    {
        Db db = new Db();
        string sql2 = "Select goodsid as 流水号,productname as 通用名,companyname_sc as 生产企业,开封市,洛阳市, 平顶山市,安阳市,鹤壁市,新乡市,焦作市,濮阳市,许昌市,漯河市,三门峡市,南阳市,商丘市,信阳市,周口市,驻马店市,济源市 From v_统计配送";
        System.Data.IDataReader dr = db.DataReader(sql2);
        this.GridView2.DataSource = dr;

        this.GridView2.DataBind();
        dr.Close();
        dr.Dispose();
        db.Close();
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "GB2312";
        // 添加头信息，为"文件下载/另存为"对话框指定默认文件名
        Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("统计配送.xls"));
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        Response.ContentType = "application/ms-excel";
        this.EnableViewState = false;
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
        this.GridView2.RenderControl(oHtmlTextWriter);

        // 把文件流发送到客户端 
        Response.Write(oStringWriter.ToString());
        // 停止页面的执行

        Response.End();
    }
    public override void VerifyRenderingInServerForm(Control control)
    {
    }



    //        if ((Request.QueryString["SupplyGoodsId"] != "") && (Request.QueryString["projectid"] != ""))
    //        {
    //            strSupplyGoodsId = SysFun.ToName(Request.QueryString["SupplyGoodsId"].ToString());
    //            strProjectid = SysFun.ToName(Request.QueryString["projectid"].ToString());
    //            string strSql = "select GoodsId,ProductName,GoodsName,MedicineModel,Outlookc,Unit,Pack,Factor,BidPrice,CompanyName_TB,CompanyName_SC,AccountId_PS,CompanyName_PS,Case GoodsState When '0' Then '竞价限价' WHEN '1' Then '备案采购(中标价为企业自主报价)' WHEN '2' then '不竞不议' END AS GoodsState,UserCode_SC,UserCode_TB,UserCode_PS,GoodsName,IsNull(Unit,'')+'/'+IsNull(PackUnit,'') As Pack1,QualityLevels As QualityLevels1,CASE QualityLevels WHEN '1' THEN '专利药品' WHEN '2' THEN '原研药品' WHEN '3' THEN '单独定价药品' WHEN '4' THEN '优质优价' WHEN '5' THEN 'GMP药品' END AS QualityLevels from SupplyPlan_Goods where ProjectId=" + strProjectid + " And IsUse=1 And SupplyGoodsId=" + strSupplyGoodsId;
    //            Db ddd = new Db();
    //            DataTable dataa = ddd.DataTable(strSql);
    //            ddd.Close();
    //            goodsid.Text = dataa.Rows[0]["goodsid"].ToString();
    //            productname.Text = dataa.Rows[0]["ProductName"].ToString();
    //            goodsname.Text = dataa.Rows[0]["goodsname"].ToString();
    //            Medicinemodel.Text = dataa.Rows[0]["MedicineModel"].ToString();
    //            outlookc.Text = dataa.Rows[0]["Outlookc"].ToString();
    //            QualityLevels.Text = dataa.Rows[0]["QualityLevels"].ToString();
    //            Factor.Text = dataa.Rows[0]["Factor"].ToString();
    //            Pack1.Text = dataa.Rows[0]["Pack1"].ToString();
    //            BidPrice.Text = dataa.Rows[0]["BidPrice"].ToString();
    //            CompanyName_TB.Text = dataa.Rows[0]["CompanyName_TB"].ToString();
    //            CompanyName_SC.Text = dataa.Rows[0]["CompanyName_SC"].ToString();
    //            CompanyName_ps.Text = dataa.Rows[0]["CompanyName_ps"].ToString();
    //            _strAccountid_ps = dataa.Rows[0]["AccountId_PS"].ToString();
    //            GoodsState.Text = dataa.Rows[0]["GoodsState"].ToString();

    //            dataa.Clear();
    //            if (Request.Form["subDel"] == "删除")
    //            {
    //                del();
    //            }
    //            if (Request.Form["subSave"] == "保  存")
    //            {
    //                Save();
    //            }
    //            GridViewShow();
    //        }

    //    }
    //}
    ////private void GridViewShow()
    ////{
    ////    int i = 0;
    ////    Db db = new Db();
    ////    DbPage dbPage = new DbPage(db);
    ////    dbPage.Table = "SupplyPlan_GoodsNext";                                               //要查询的表明
    //    dbPage.Key = "id";                                               //表的主键    
    //    dbPage.Where = fctConditions();                                        //查询条件    
    //    dbPage.SelectField = "*";//要显示的字段
    //    dbPage.PageIndex = SysFun.ToInt(Request["CurrentlyPageIndex"]);          //要显示第几页   
    //    dbPage.PageSize = 20;                                                    //没有显示记录条数   
    //    dbPage.OrderBy = "areaid,id";                  //排序字段

    //    System.Text.StringBuilder strHtml = new System.Text.StringBuilder();
    //    strHtml.Append("<table width='100%' border='0.5' align='left' cellpadding='2' cellspacing='1' class='table_table' id='Myta'>");
    //    strHtml.Append("<tr>");
    //    strHtml.Append("<td    width='20%'>地区</td>");
    //    strHtml.Append("<td   width='50%'>配送企业</td>");
    //    strHtml.Append("<td    width='15%'>删除</td>");
    //    strHtml.Append("</tr>");
    //    System.Data.IDataReader dr = dbPage.ExecuteReader();
    //    while (dr.Read())
    //    {
    //        if (i == 0)
    //        {
    //            strHtml.Append("<tr class='table_td'>");
    //            i++;
    //        }
    //        else
    //        {
    //            strHtml.Append("<tr class='table_td2'>");
    //            i--;
    //        }
    //        strHtml.Append("<td width='20%'>" + dr["areaname"].ToString() + "</td>");
    //        if (dr["Accountid_ps_B1"].ToString() != "")
    //        {
    //            strHtml.Append("<td width='50%' align='center' ><input type='hidden' name='companyname_ps_B1" + dr["id"] + "' value='" + dr["accountid_ps_b1"] + "'>" + dr["companyname_ps_B1"].ToString() + "</td>");
    //        }
    //        else
    //        {
    //            strHtml.Append("<td width='50%' align='center'  Style=\"color:Red;cursor:hand\"><a href='TraSelectSuperviseNextArea.aspx>添加</a></td>");
    //        }
    //        if (dr["PS_ChangeStatus"].ToString() != "1")
    //        {
    //            strHtml.Append("<td><input  type='checkbox' name='id' value='" + SysFun.ToTrim(dr["id"]) + "'</td>");
    //        }
    //        else
    //        {
    //            strHtml.Append("<td></td>");
    //        }
    //        strHtml.Append("</tr>");
    //    }
    //    strHtml.Append("</table>");
    //    Label1.Text = strHtml.ToString();

    //    dr.Close();
    //    dr.Dispose();
    //    dbPage.Db.Close();
    //    db.Close();
    //    this.DbPageFlip1.DbPage = dbPage;//将数据传给控键



    ////}
    //public string fctConditions()
    //{
    //    string _strTemp = " SupplyGoodsId=" + strSupplyGoodsId + " and Accountid_ps_M=" + _strAccountid;
    //    if ((Request.Form["txtAreaId"] != "") && (Request.Form["txtAreaId"] != null))
    //    {
    //        _strTemp += " and areaid=" + Request.Form["txtAreaId"].ToString();
    //    }
    //    return _strTemp;
    //}
    ////private void Save()
    //{
    //    string strAreaname = "", strareaid = "", straccountid_ps = "", strcompanyname_ps = "", strSql = "", strSql2 = "", strSql3 = "";
    //    bool falg = false;
    //    if (Request.Form["txtCompanyName_PS"].ToString() != "")
    //    {
    //        strcompanyname_ps = Request.Form["txtCompanyName_PS"].ToString();
    //        straccountid_ps = Request.Form["txtAccountId_PS"].ToString();

    //        strAreaname = lstAreaName.SelectedItem.Text;
    //        strareaid = lstAreaName.SelectedValue;

    //        strSql2 = "select count(*) as cnn from SupplyPlan_GoodsNext where SupplyGoodsId=" + strSupplyGoodsId + " and  Accountid_ps_M=" + _strAccountid + " and Accountid_ps_B1=" + straccountid_ps + " and areaid=" + strareaid;
    //        strSql = "select count(*) as cnn from SupplyPlan_GoodsNext where SupplyGoodsId=" + strSupplyGoodsId + " and Accountid_ps_B1<>" + _strAccountid + " and Accountid_ps_M=" + _strAccountid + " and areaid=" + strareaid;
    //        strSql3 = "select number_ps from area where areaid=" + strareaid;
    //        Db ddddd = new Db();
    //        DataTable dt = ddddd.DataTable(strSql);
    //        DataTable dt2 = ddddd.DataTable(strSql2);
    //        DataTable dt3 = ddddd.DataTable(strSql3);
    //        ddddd.Close();
    //        int num = Convert.ToInt32(dt3.Rows[0][0].ToString());
    //        if (Convert.ToInt32((dt.Rows[0][0])) > num)
    //        {
    //            Response.Write("<script>alert('在该地区的配送企业已达最多数！');</script>");
    //        }
    //        else if (Convert.ToInt32((dt2.Rows[0][0])) > 0)
    //        {
    //            Response.Write("<script>alert('在该地区的该配送企业已经添加过了！');</script>");
    //        }
    //        else
    //        {
    //            if ((Convert.ToInt32((dt.Rows[0][0])) > num - 1) && (_strAccountid == straccountid_ps))
    //            {
    //                strSql = "if exists (select supplygoodsid from supplyplan_goods where supplygoodsid=" + strSupplyGoodsId + " and accountid_ps=" + _strAccountid + ")  insert into SupplyPlan_GoodsNext values('" + strSupplyGoodsId + "','" + _strAccountid + "','" + CompanyName_ps.Text + "','" + straccountid_ps + "','" + strcompanyname_ps + "','" + strareaid + "','" + strAreaname + "',0,null,'','','') ";
    //                falg = true;
    //            }
    //            else if ((Convert.ToInt32((dt.Rows[0][0])) > num - 1) && (_strAccountid != straccountid_ps))
    //            {
    //                Response.Write("<script>alert('在该地区的配送企业已达最多数！');</script>");
    //            }
    //            else
    //            {
    //                strSql = "if exists (select supplygoodsid from supplyplan_goods where supplygoodsid=" + strSupplyGoodsId + " and accountid_ps=" + _strAccountid + ")  insert into SupplyPlan_GoodsNext values('" + strSupplyGoodsId + "','" + _strAccountid + "','" + CompanyName_ps.Text + "','" + straccountid_ps + "','" + strcompanyname_ps + "','" + strareaid + "','" + strAreaname + "',0,null,'','','') ";
    //                falg = true;
    //            }
    //            if (falg)
    //            {
    //                Db dbb = new Db();
    //                int kk = dbb.Command(strSql);

    //                dbb.Close();

    //                if (kk > 0)
    //                {
    //                    HZ.MBSM.Model.Account _Account = (HZ.MBSM.Model.Account)Session["Account"];
    //                    HZ.MBSM.BLL.LogManage.RecordLoginDetail(_Account.AccountName, Request.UserHostAddress, "插入", "SupplyPlan_GoodsNext", strSql, "../Bidder/TraSelectSuperviseNextArea.aspx");
    //                    Response.Write("<script>alert('添加成功！');</script>");
    //                }
    //                else
    //                {
    //                    Response.Write("<script>alert('添加失败,请检查您的身份是否是此药品的主配送人！');</script>");
    //                }
    //                GridViewShow();
    //            }
    //        }
    //        dt.Clear();
    //        dt2.Clear();
    //    }
    //    else
    //    {
    //        Response.Write("<script>alert('配送企业不允许为空！请选择配送企业');</script>");
    //    }
    //}
    ////private void del()
    ////{
    ////    string strSql = "", strId;
    ////    string[] Id ={ "" };
    ////    if (Request.Form["id"] != "")
    ////    {
    ////        HZ.MBSM.Model.Account _Account = (HZ.MBSM.Model.Account)Session["Account"];
    ////        strId = Request.Form["id"].ToString();
    ////        Id = strId.Split(',');
    ////        for (int i = 0; i < Id.Length; i++)
    ////        {
    ////            strSql += "@" + "delete from SupplyPlan_GoodsNext where Id in (" + Id[i].ToString() + ") and  SupplyGoodsId not in(select distinct SupplyGoodsId from PurchaseTable where areaid=" + lstAreaName.SelectedValue + " and accountid_ps =" + Request.Form["companyname_ps_B1" + Id[i].ToString() + ""].ToString() + ") and PS_ChangeStatus<>1 ";
    ////        }
    ////        Db db = new Db();
    ////        if (strSql.Length > 0)
    ////        {
    ////            db.ExecuteTransaction(strSql.Substring(1).Split(new char[] { '@' }), 30);
    ////        }
    ////        db.Close();

    ////        HZ.MBSM.BLL.LogManage.RecordLoginDetail(_Account.AccountName, Request.UserHostAddress, "删除", "SupplyPlan_GoodsNext", strSql, "../Bidder/TraSelectSuperviseNextArea.aspx");
    ////    }

    ////}
    ///// <summary>
    ///// 生成查询条件
    ///// </summary>
    ///// <returns></returns>

    //#region TraDes 成员

    //public string Name
    //{
    //    get { return "维护配送企业"; }
    //}

    //public string Describe
    //{
    //    get { return ""; }
    //}

    //public string ParentName
    //{
    //    get { return "维护配送企业"; }
    //}

    //public System.Collections.Generic.List<HZ.MBSM.DAL.AccountType> UseAccountType
    //{
    //    get
    //    {
    //        System.Collections.Generic.List<HZ.MBSM.DAL.AccountType> _AccountType = new System.Collections.Generic.List<HZ.MBSM.DAL.AccountType>();
    //        _AccountType.Add(HZ.MBSM.DAL.AccountType.enumCompany);
    //        return _AccountType;
    //    }
    //}

    //#endregion

    protected void Button2_Click(object sender, EventArgs e)
    {
        printWord();
    }
    protected void Button3_Click(object sender, EventArgs e)
    {
        string sql3 = @"
select usercode_tb as 企业编号,companyname_tb 企业名称,
 sum( case projectid when 8 then 1 else 0 end)  as 原中标,
sum( case projectid when 9 then 1 else 0 end)  as 自行采购,
sum( case projectid when 10 then 1 else 0 end)  as [22中降价品种],
  sum( case projectid when 11 then 1 else 0 end)  as 血液制品,
 sum( case projectid when 12 then 1 else 0 end)  as 重点监控
 from SupplyPlan_Goods where isuse=1
group by usercode_tb,companyname_tb";
        Db db = new Db();
        System.Data.IDataReader dr = db.DataReader(sql3);
        this.GridView2.DataSource = dr;
        this.GridView2.DataBind();
        dr.Dispose();
        dr.Close();
        db.Close();
        Response.Clear();
        Response.Buffer = true;
        Response.Charset = "GB2313";
        Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlEncode("中标情况.xls"));
        Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        Response.ContentType = "application/ms-excel";
        this.EnableViewState = false;
        System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
        this.GridView2.RenderControl(oHtmlTextWriter);

        // 把文件流发送到客户端 
        Response.Write(oStringWriter.ToString());
        // 停止页面的执行

        Response.End();
        //Response.Clear();
        //Response.Buffer = true;
        //Response.Charset = "GB2313";
        //Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpUtility.UrlDecode("钟表情况.xls"));
        //Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
        //Response.ContentType = "application/ms-excel";
        //this.EnableViewState = false;
        //System.IO.StringWriter ostringwrite = new System.IO.StringWriter();
        //System.Web.UI.HtmlTextWriter ohtml = new HtmlTextWriter(ostringwrite);
        //this.GridView2.RenderControl(ohtml);
        //Response.Write(ostringwrite.ToString());
        //Response.End();
    }
}
